import mysqlx
import pymysql
import redis

class DB():
    DBHOST = 'localhost'
    DBUSER = 'root'
    DBPASS = '123456'
    DBNAME = 'test'
    # 下面是尝试连接mysql数据库
    try:
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        print('数据库连接成功!')
    except pymysql.Error as e:
        print('数据库连接失败'+str(e))

    # def checkall():
    #     DBHOST = 'localhost'
    #     DBUSER = 'root'
    #     DBPASS = '123456'
    #     DBNAME = 'test'
    #     db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
    #     cursor = db.cursor()
    #     sql="select * from C;"
    #     cursor.execute(sql)
    #     results = cursor.fetchall()
    #     data=[]
    #     for row in results:
    #         data.append(row)


    def setting(cname,cno,credit):
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '123456'
        DBNAME = 'test'
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        cursor = db.cursor()
        sql = "insert into C values(%s,%s,%s)"
        param=(cno,cname,credit)
        cursor.execute(sql,param)
        db.commit()
    def changing(sno,cno,grade):
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '123456'
        DBNAME = 'test'
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        cursor = db.cursor()
        #sql = "insert into SC values(%s,%s,%s)"
        sql = "update SC set grade= %s where sno= %s and cno=%s"
        param = (grade,sno,cno)
        cursor.execute(sql, param)
        db.commit()
    def delete(sno,cno):
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '123456'
        DBNAME = 'test'
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        cursor = db.cursor()
        sql = "delete from SC where sno= %s and cno= %s"
        param = (sno,cno)
        cursor.execute(sql, param)
        db.commit()
    def insert(sno,cno,grade):
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '123456'
        DBNAME = 'test'
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        cursor = db.cursor()
        try:
            sql = "insert into SC values(%s,%s,%s)"
            param = (sno, cno, grade)
            cursor.execute(sql, param)
        except pymysql.err.IntegrityError:
            return 0


        db.commit()
    def search(sno,key):
        # DBHOST = 'localhost'
        # DBUSER = 'root'
        # DBPASS = '123456'
        # DBNAME = 'test'
        # db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        # cursor = db.cursor()
        # sql = "select * from S;"
        # cursor.execute(sql)
        # results = cursor.fetchall()
        # dict={}
        # for row in results:
        #     dict.update({row[0]:row[2]})
        # TK=dict[sno]
        # if TK==key:
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '123456'
        DBNAME = 'test'
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        cursor = db.cursor()
        sql = "select C.cname,SC.grade from C,SC where C.cno=SC.cno and sno=%s"
        param = sno
        cursor.execute(sql, param)
        results = cursor.fetchall()
        data = []
        for row in results:
            data.append(row)
        db.commit()
        return data
        #     cursor = db.cursor()
        #     sql = "select C.cname,SC.grade from C,SC where C.cno=SC.cno and sno=%s"
        #     param = sno
        #     cursor.execute(sql, param)
        #     results = cursor.fetchall()
        #     data = []
        #     for row in results:
        #         data.append(row)
        #     db.commit()
        #     return data
        # else :
        #     return 0


    def test():
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '123456'
        DBNAME = 'test'
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        cursor = db.cursor()
        sql = "select C.cname,SC.grade from C,SC where C.cno=SC.cno and sno=%s"
        param ="02"
        cursor.execute(sql, param)
        results = cursor.fetchall()
        data=[]
        for row in results:
            data.append(row)
        print(data)
        db.commit()
    def SC(self):#用于修改成绩模块
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '123456'
        DBNAME = 'test'
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        cursor = db.cursor()
        sql = "select C.cname,SC.cno,SC.sno,SC.grade from sc,c where sc.cno=c.cno "
        cursor.execute(sql)
        results = cursor.fetchall()
        data = []
        for row in results:
            data.append(row)
        #print(data)
        db.commit()
        return data

    def S(self):#用于录入成绩模块,删除
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '123456'
        DBNAME = 'test'
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        cursor = db.cursor()
        sql = "select s.sname,SC.sno,sc.cno,SC.grade from sc,s where sc.sno=s.sno "
        cursor.execute(sql)
        results = cursor.fetchall()
        data = []
        for row in results:
            data.append(row)
        db.commit()
        return data

    def C(self):#用于设置
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '123456'
        DBNAME = 'test'
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        cursor = db.cursor()
        sql = "select * from c "
        cursor.execute(sql)
        results = cursor.fetchall()
        data = []
        for row in results:
            data.append(row)
        print(data)
        db.commit()
        return data

    def grade(depart,major,classroom):  #用于统计
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '123456'
        DBNAME = 'test'
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        cursor = db.cursor()
        #sql = "select sc.grade from sc,s where sc.sno=s.sno and depart=%s and major=%s and classroom=%s"
        #sql="select sc.grade from sc,s where sc.sno=s.sno and s.depart=pa and s.major=a and s.classroom=1;"
        #sql = "select SC.grade from S,SC where S.sno=SC.sno and depart=%s and major=%s and classroom=%s;"
        sql="select sc.grade from sc,s where sc.sno=s.sno and depart= %s and major= %s and classroom=%s"
        param = (depart,major,classroom)
        cursor.execute(sql,param)
        results = cursor.fetchall()
        data = []
        for row in results:
            data.append(row)
        print(data)
        db.commit()
        return data
    def excel(self):#用于生成excel
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '123456'
        DBNAME = 'test'
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        cursor = db.cursor()
        sql = "select s.sname,sc.grade from sc,s,c where sc.sno=s.sno and c.cno=sc.cno and c.cname=%s"
        param = self
        cursor.execute(sql, param)
        results = cursor.fetchall()
        data = []
        for row in results:
            data.append(row)
        db.commit()
        return data
    def testRedis(self):
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '123456'
        DBNAME = 'test'
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        cursor = db.cursor()
        sql = "select * from root"
        cursor.execute(sql)
        results = cursor.fetchall()
        data = []
        for row in results:
            data.append(row)
        print(data[0][1])
        r = redis.Redis(host='127.0.0.1', port=6379, decode_responses=True)
        r.set(data[0][1], data[0][1])  # 设置 name 对应的值
        db.commit()
        return r.get(data[0][1])


if __name__ == '__main__':
    DB.testRedis(1)

